const express = require("express");
const router = express.Router();
const db = require("../db");
const { generateUniqueId } = require("../public/javascripts/index");
const updatePlan = (planId, status, res) => {
  // 对客户端的数据进行校验
  if (!planId) {
    return res.send({
      success: false,
      message: "编码不能为空",
    });
  }

  // 获取现有记录
  const selectSQL = "SELECT * FROM selection_plan WHERE planId=?";
  db.query(selectSQL, [planId], (selectError, selectResult) => {
    if (selectError) {
      return res.cc(selectError);
    }

    if (selectResult.length === 0) {
      return res.cc("未找到该记录!");
    }

    const updateSQL = "UPDATE selection_plan SET status = 4 WHERE planId = ?";
    db.query(updateSQL, [planId], (updateError, updateResult) => {
      if (updateError) {
        return res.cc(updateError);
      }
      if (updateResult.affectedRows !== 1) {
        return res.cc("记录保存失败！");
      }
      return res.cc("记录保存成功", true);
    });
  });
};

router.post("/add", (req, res) => {
  // 执行定义好的登录函数
  const info = req.body;

  // 简单校验参数是否存在
  if (
    !info.planId ||
    !info.planName ||
    !info.businessName ||
    !info.businessId ||
    !info.creator ||
    !info.warehouseName ||
    !info.warehouseId
  ) {
    return res.cc("参数不完整");
  }

  // 定义 SQL 语句使用参数化查询
  const sql = "INSERT INTO selection_history SET ?";
  const params = {
    extractId: generateUniqueId(),
    planId: info.planId,
    planName: info.planName,
    businessName: info.businessName,
    businessId: info.businessId,
    creator: info.creator,
    warehouseName: info.warehouseName,
    warehouseId: info.warehouseId,
    createdTime: Date.now(),
  };

  db.query(sql, params, (error, result) => {
    if (error) {
      return res.cc(error);
    }

    // 判断影响行数是否为 1
    if (result.affectedRows !== 1) {
      return res.cc("记录保存失败！");
    }

    // 更新相关计划的状态
    updatePlan(info.planId, info.status, res);
  });
});

router.delete("/delete", (req, res) => {
  const info = req.body;
  //对客户端的数据进行校验
  if (!info.extractId) {
    return res.send({
      success: false,
      message: "编码不能为空",
    });
  }
  // 执行定义好的登录函数
  let sql = "select * from selection_history where extractId=?";
  db.query(sql, [info.extractId], (error, result) => {
    if (error) {
      return res.cc(error);
    }
    if (result.length === 0) {
      return res.cc("未找到该记录!");
    }
    const planId = result[0].planId;
    // 定义 SQL 语句
    const sql1 = "delete from selection_history where extractId=?";
    // 调用 db.query() 执行 sql 语句
    db.query(sql1, info.extractId, (error, result) => {
      if (error) return res.cc(error);
      // 判断影响行数是否为 1
      if (result.affectedRows !== 1) return res.cc("删除失败！");
      // 更新 selection_plan 表中对应 planId 的 status 为 1
      const sqlUpdatePlan =
        "UPDATE selection_plan SET status = 1 WHERE planId=?";
      db.query(sqlUpdatePlan, [planId], (error, result) => {
        if (error) {
          return res.cc(error);
        }
        if (result.affectedRows !== 1) {
          return res.cc("删除失败！");
        }

        return res.cc("删除成功", true);
      });
    });
  });
});

router.post("/list", (req, res) => {
  const {
    page = 1,
    limit = 10,
    planName = "",
    businessName = "",
    creator = "",
    createdTime = "",
  } = req.body;
  const offset = (page - 1) * limit;

  // 构建基础查询和参数
  let baseQuery = " FROM selection_history WHERE 1=1";
  let params = [];

  if (planName) {
    baseQuery += " AND planName LIKE ?";
    params.push(`%${planName}%`);
  }
  if (businessName) {
    baseQuery += " AND businessName LIKE ?";
    params.push(`%${businessName}%`);
  }
  if (creator) {
    baseQuery += " AND creator LIKE ?";
    params.push(`%${creator}%`);
  }
  if (
    createdTime &&
    createdTime.length === 2 &&
    createdTime[0] &&
    createdTime[1]
  ) {
    baseQuery += ` AND createdTime >= ? AND createdTime <= ?`;
    params.push(createdTime[0], createdTime[1]);
  }
  // 构建 SQL 计数查询语句
  const sqlCount = `SELECT COUNT(*) AS total ${baseQuery}`;

  // 查询总条数
  db.query(sqlCount, params, (error, countResults) => {
    if (error) {
      return res.cc(error);
    }

    const total = countResults[0].total;

    // 构建 SQL 查询语句
    let sql = `SELECT * ${baseQuery} LIMIT ? OFFSET ?`;
    params.push(limit, offset);

    // 执行查询
    db.query(sql, params, (error, results) => {
      if (error) {
        return res.cc(error);
      }
      return res.cc("获取记录成功", true, results, {
        total,
        page,
        limit,
      });
    });
  });
});

router.get("/findById", (req, res) => {
  const info = req.query;

  // 对客户端的数据进行校验
  if (!info.extractId && !info.planId) {
    return res.send({
      success: false,
      message: "编码不能为空",
    });
  }

  // 执行定义好的登录函数
  let sql, queryParams;
  if (info.extractId) {
    sql = "SELECT * FROM selection_history WHERE extractId=?";
    queryParams = [info.extractId];
  } else if (info.planId) {
    sql = "SELECT * FROM selection_history WHERE planId=?";
    queryParams = [info.planId];
  }

  db.query(sql, queryParams, (error, result) => {
    if (error) {
      return res.cc(error);
    }
    if (result.length === 0) {
      return res.cc("未找到该记录!");
    }
    return res.cc("获取详情成功", true, result[0]);
  });
});

module.exports = router;
